/*--This program will pull the number of PIKs overlapping two 
datasets across the years of study. The table will look like the following:

Denominator/	Numerator
		        1940	1969	1974	1979	1984	1989	1994   2000
1940 Census	  N
1969 Taxes		      N
1974 Taxes		            N
1979 Taxes			                N
1984 Taxes				                  N
1989 Taxes						                   N
1994 Taxes                                         N                        
2000 Census							                            N

The unit of analysis in the program is the var26. 

The following assumptions and transformations are made:
1. The datasets are trimmed down to only the var26 column.
2. The PIKs are deduplicated, and missing values have been dropped.*/

/*Setting the libraries and calling the data files*/

libname temp40 'path13';
DATA cen40t;
 SET temp40.file14 (KEEP=var26);
RUN;
PROC SQL;
CREATE TABLE cen40 AS SELECT DISTINCT(var26) AS var26 FROM cen40t WHERE var26 IS NOT NULL;
QUIT;

libname temp69 'path1';
DATA tax69t;
   SET temp69.file1 (KEEP=var1);
RUN;
PROC SQL;
CREATE TABLE tax69 AS SELECT DISTINCT(var1) AS var1 FROM tax69t WHERE var1 IS NOT NULL;
QUIT;

libname temp74 'path2';
DATA tax74a (RENAME = (var2 = var1));
   SET temp74.file2 (KEEP=var2);
DATA tax74b (RENAME = (var3 = var1));
   SET temp74.file2 (KEEP=var3);
DATA tax74t;
   SET tax74a tax74b;
RUN;
PROC SQL;
CREATE TABLE tax74 AS SELECT DISTINCT(var1) AS var1 FROM tax74t WHERE var1 IS NOT NULL;

libname temp79 'path3';
DATA tax79a (RENAME = (var5 = var26));
   SET temp79.file3 (KEEP=var5);
DATA tax79b (RENAME = (var6 = var26));
   SET temp79.file3 (KEEP=var6);
DATA tax79t;
   SET tax79a tax79b;
RUN;
PROC SQL;
CREATE TABLE tax79 AS SELECT DISTINCT(var26) AS var26 FROM tax79t WHERE var26 IS NOT NULL;
QUIT;

libname temp84 'path4';
DATA tax84a (RENAME = (var5 = var26));
   SET temp84.file4 (KEEP=var5);
DATA tax84b (RENAME = (var6 = var26));
   SET temp84.file4 (KEEP=var6);
DATA tax84t;
   SET tax84a tax84b;
RUN;
PROC SQL;
CREATE TABLE tax84 AS SELECT DISTINCT(var26) AS var26 FROM tax84t WHERE var26 IS NOT NULL;
QUIT;

libname temp89 'path5';
DATA tax89a (RENAME = (var5 = var26));
   SET temp89.file5 (KEEP=var5);
DATA tax89b (RENAME = (var6 = var26));
   SET temp89.file5 (KEEP=var6);
DATA tax89t;
   SET tax89a tax89b;
RUN;
PROC SQL;
CREATE TABLE tax89 AS SELECT DISTINCT(var26) AS var26 FROM tax89t WHERE var26 IS NOT NULL;
QUIT;

libname temp94 'path6';
DATA tax94a (RENAME = (var5 = var26));
   SET temp94.file6 (KEEP=var5);
DATA tax94b (RENAME = (var6 = var26));
   SET temp94.file6 (KEEP=var6);
DATA tax94c (RENAME = (var8 = var26));
   SET temp94.file6 (KEEP=var8);
DATA tax94d (RENAME = (var9 = var26));
   SET temp94.file6 (KEEP=var9);
DATA tax94e (RENAME = (var10 = var26));
   SET temp94.file6 (KEEP=var10);
DATA tax94f (RENAME = (var11 = var26));
   SET temp94.file6 (KEEP=var11);
DATA tax94t;
   SET tax94a tax94b tax94c tax94d tax94e tax94f;
RUN;
PROC SQL;
CREATE TABLE tax94 AS SELECT DISTINCT(var26) AS var26 FROM tax94t WHERE var26 IS NOT NULL;
QUIT;

libname temp00 'path14';
DATA cen00t;
 SET temp00.file15 (KEEP=var26);
RUN;
PROC SQL;
CREATE TABLE cen00 AS SELECT DISTINCT(var26) AS var26 FROM cen00t WHERE var26 IS NOT NULL;
QUIT;

/*Assigning a library for output*/
libname out 'YOUR_FILEPATH';
RUN;

/*Joining the tax data with the census crosswalks*/

PROC SQL;
CREATE TABLE out.centaxjoinallyears AS

/*The following defines the 1940 row*/
SELECT
COUNT(cen40.var26) AS Cen1940,
COUNT(tax69.var1)*100/COUNT(cen40.var26) AS Tax1969,
COUNT(tax74.var1)*100/COUNT(cen40.var26) AS Tax1974,
COUNT(tax79.var26)*100/COUNT(cen40.var26) AS Tax1979,
COUNT(tax84.var26)*100/COUNT(cen40.var26) AS Tax1984,
COUNT(tax89.var26)*100/COUNT(cen40.var26) AS Tax1989,
COUNT(tax94.var26)*100/COUNT(cen40.var26) AS Tax1994,
COUNT(cen00.var26)*100/COUNT(cen40.var26) AS Cen2000
FROM cen40
LEFT JOIN tax69
ON cen40.var26=tax69.var1
LEFT JOIN tax74
ON cen40.var26=tax74.var1
LEFT JOIN tax79
ON cen40.var26=tax79.var26
LEFT JOIN tax84
ON cen40.var26=tax84.var26
LEFT JOIN tax89
ON cen40.var26=tax89.var26
LEFT JOIN tax94
ON cen40.var26=tax94.var26
LEFT JOIN cen00
ON cen40.var26=cen00.var26

UNION ALL

/*The following defines the 1969 row*/
SELECT
COUNT(cen40.var26)*100/COUNT(tax69.var1) AS Cen1940,
COUNT(tax69.var1) AS Tax1969,
COUNT(tax74.var1)*100/COUNT(tax69.var1) AS Tax1974,
COUNT(tax79.var26)*100/COUNT(tax69.var1) AS Tax1979,
COUNT(tax84.var26)*100/COUNT(tax69.var1) AS Tax1984,
COUNT(tax89.var26)*100/COUNT(tax69.var1) AS Tax1989,
COUNT(tax94.var26)*100/COUNT(tax69.var1) AS Tax1994,
COUNT(cen00.var26)*100/COUNT(tax69.var1) AS Cen2000
FROM tax69
LEFT JOIN cen40
ON tax69.var1=cen40.var26
LEFT JOIN tax74
ON tax69.var1=tax74.var1
LEFT JOIN tax79
ON tax69.var1=tax79.var26
LEFT JOIN tax84
ON tax69.var1=tax84.var26
LEFT JOIN tax89
ON tax69.var1=tax89.var26
LEFT JOIN tax94
ON tax69.var1=tax94.var26
LEFT JOIN cen00
ON tax69.var1=cen00.var26

UNION ALL

/*The following defines the 1974 row*/
SELECT
COUNT(cen40.var26)*100/COUNT(tax74.var1) AS Cen1940,
COUNT(tax69.var1)*100/COUNT(tax74.var1) AS Tax1969,
COUNT(tax74.var1) AS Tax1974,
COUNT(tax79.var26)*100/COUNT(tax74.var1) AS Tax1979,
COUNT(tax84.var26)*100/COUNT(tax74.var1) AS Tax1984,
COUNT(tax89.var26)*100/COUNT(tax74.var1) AS Tax1989,
COUNT(tax94.var26)*100/COUNT(tax74.var1) AS Tax1994,
COUNT(cen00.var26)*100/COUNT(tax74.var1) AS Cen2000
FROM tax74
LEFT JOIN cen40
ON tax74.var1=cen40.var26
LEFT JOIN tax69
ON tax74.var1=tax69.var1
LEFT JOIN tax79
ON tax74.var1=tax79.var26
LEFT JOIN tax84
ON tax74.var1=tax84.var26
LEFT JOIN tax89
ON tax74.var1=tax89.var26
LEFT JOIN tax94
ON tax74.var1=tax94.var26
LEFT JOIN cen00
ON tax74.var1=cen00.var26

UNION ALL

/*The following defines the 1979 row*/
SELECT
COUNT(cen40.var26)*100/COUNT(tax79.var26) AS Cen1940,
COUNT(tax69.var1)*100/COUNT(tax79.var26) AS Tax1969,
COUNT(tax74.var1)*100/COUNT(tax79.var26) AS Tax1974,
COUNT(tax79.var26) AS Tax1979,
COUNT(tax84.var26)*100/COUNT(tax79.var26) AS Tax1984,
COUNT(tax89.var26)*100/COUNT(tax79.var26) AS Tax1989,
COUNT(tax94.var26)*100/COUNT(tax79.var26) AS Tax1994,
COUNT(cen00.var26)*100/COUNT(tax79.var26) AS Cen2000
FROM tax79
LEFT JOIN cen40
ON tax79.var26=cen40.var26
LEFT JOIN tax69
ON tax79.var26=tax69.var1
LEFT JOIN tax74
ON tax79.var26=tax74.var1
LEFT JOIN tax84
ON tax79.var26=tax84.var26
LEFT JOIN tax89
ON tax79.var26=tax89.var26
LEFT JOIN tax94
ON tax79.var26=tax94.var26
LEFT JOIN cen00
ON tax79.var26=cen00.var26

UNION ALL

/*The following defines the 1984 row*/
SELECT
COUNT(cen40.var26)*100/COUNT(tax84.var26) AS Cen1940,
COUNT(tax69.var1)*100/COUNT(tax84.var26) AS Tax1969,
COUNT(tax74.var1)*100/COUNT(tax84.var26) AS Tax1974,
COUNT(tax79.var26)*100/COUNT(tax84.var26) AS Tax1979,
COUNT(tax84.var26) AS Tax1984,
COUNT(tax89.var26)*100/COUNT(tax84.var26) AS Tax1989,
COUNT(tax94.var26)*100/COUNT(tax84.var26) AS Tax1994,
COUNT(cen00.var26)*100/COUNT(tax84.var26) AS Cen2000
FROM tax84
LEFT JOIN cen40
ON tax84.var26=cen40.var26
LEFT JOIN tax69
ON tax84.var26=tax69.var1
LEFT JOIN tax74
ON tax84.var26=tax74.var1
LEFT JOIN tax79
ON tax84.var26=tax79.var26
LEFT JOIN tax89
ON tax84.var26=tax89.var26
LEFT JOIN tax94
ON tax84.var26=tax94.var26
LEFT JOIN cen00
ON tax84.var26=cen00.var26

UNION ALL

/*The following defines the 1989 row*/
SELECT
COUNT(cen40.var26)*100/COUNT(tax89.var26) AS Cen1940,
COUNT(tax69.var1)*100/COUNT(tax89.var26) AS Tax1969,
COUNT(tax74.var1)*100/COUNT(tax89.var26) AS Tax1974,
COUNT(tax79.var26)*100/COUNT(tax89.var26) AS Tax1979,
COUNT(tax84.var26)*100/COUNT(tax89.var26) AS Tax1984,
COUNT(tax89.var26) AS Tax1989,
COUNT(tax94.var26)*100/COUNT(tax89.var26) AS Tax1994,
COUNT(cen00.var26)*100/COUNT(tax89.var26) AS Cen2000
FROM tax89
LEFT JOIN cen40
ON tax89.var26=cen40.var26
LEFT JOIN tax69
ON tax89.var26=tax69.var1
LEFT JOIN tax74
ON tax89.var26=tax74.var1
LEFT JOIN tax79
ON tax89.var26=tax79.var26
LEFT JOIN tax84
ON tax89.var26=tax84.var26
LEFT JOIN tax94
ON tax89.var26=tax94.var26
LEFT JOIN cen00
ON tax89.var26=cen00.var26

UNION ALL

/*The following defines the 1994 row*/
SELECT
COUNT(cen40.var26)*100/COUNT(tax94.var26) AS Cen1940,
COUNT(tax69.var1)*100/COUNT(tax94.var26) AS Tax1969,
COUNT(tax74.var1)*100/COUNT(tax94.var26) AS Tax1974,
COUNT(tax79.var26)*100/COUNT(tax94.var26) AS Tax1979,
COUNT(tax84.var26)*100/COUNT(tax94.var26) AS Tax1984,
COUNT(tax89.var26)*100/COUNT(tax94.var26) AS Tax1989,
COUNT(tax94.var26) AS Tax1994,
COUNT(cen00.var26)*100/COUNT(tax94.var26) AS Cen2000
FROM tax94
LEFT JOIN cen40
ON tax94.var26=cen40.var26
LEFT JOIN tax69
ON tax94.var26=tax69.var1
LEFT JOIN tax74
ON tax94.var26=tax74.var1
LEFT JOIN tax79
ON tax94.var26=tax79.var26
LEFT JOIN tax84
ON tax94.var26=tax84.var26
LEFT JOIN tax89
ON tax94.var26=tax89.var26
LEFT JOIN cen00
ON tax94.var26=cen00.var26

UNION ALL

/*The following defines the 2000 row*/
SELECT
COUNT(cen40.var26)*100/COUNT(cen00.var26) AS Cen1940,
COUNT(tax69.var1)*100/COUNT(cen00.var26) AS Tax1969,
COUNT(tax74.var1)*100/COUNT(cen00.var26) AS Tax1974,
COUNT(tax79.var26)*100/COUNT(cen00.var26) AS Tax1979,
COUNT(tax84.var26)*100/COUNT(cen00.var26) AS Tax1984,
COUNT(tax89.var26)*100/COUNT(cen00.var26) AS Tax1989,
COUNT(tax94.var26)*100/COUNT(cen00.var26) AS Tax1994,
COUNT(cen00.var26) AS Cen2000
FROM cen00
LEFT JOIN cen40
ON cen00.var26=cen40.var26
LEFT JOIN tax69
ON cen00.var26=tax69.var1
LEFT JOIN tax74
ON cen00.var26=tax74.var1
LEFT JOIN tax79
ON cen00.var26=tax79.var26
LEFT JOIN tax84
ON cen00.var26=tax84.var26
LEFT JOIN tax89
ON cen00.var26=tax89.var26
LEFT JOIN tax94
ON cen00.var26=tax94.var26;
QUIT;

/*Making the data available outside of SAS*/
PROC EXPORT DATA=out.centaxjoinallyears OUTFILE = 'YOUR_FILEPATH/centaxjoinallyears.csv' REPLACE;
run;
